using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using CoreLab.Oracle;

namespace Common.Utilities
{
    /// <summary>
    /// utility class for managing db field data type
    /// </summary>
    [Serializable]
    public struct FieldDataType
    {
        /// <summary></summary>
        public DataProviderType Provider;
        /// <summary></summary>
        public System.Data.SqlDbType DataType;
        /// <summary></summary>
        public int Size;
        /// <summary></summary>
        public int Precision;
        /// <summary></summary>
        public int Scale;
        /// <summary></summary>
        public bool Nullible;

        /// <summary>
        /// convert oracle field data type to mssql field data type
        /// </summary>
        /// <param name="oracleDbType"></param>
        /// <returns></returns>
        public static System.Data.SqlDbType ToMssqlFieldDataType(OracleDbType oracleDbType)
        {
            return DbTypeConverter.GetSqlDbTypeFromOracleDbType(oracleDbType);
            //System.Data.SqlDbType sqlDbType = System.Data.SqlDbType.NVarChar;
            //switch (oracleDbType)
            //{
            //    case OracleDbType.BFile:
            //        sqlDbType = System.Data.SqlDbType.Binary;
            //        break;
            //    case OracleDbType.BinaryDouble:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.BinaryFloat:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.Blob:
            //        sqlDbType = System.Data.SqlDbType.Binary;
            //        break;
            //    case OracleDbType.Byte:
            //        sqlDbType = System.Data.SqlDbType.Binary;
            //        break;
            //    case OracleDbType.Char:
            //        sqlDbType = System.Data.SqlDbType.Char;
            //        break;
            //    case OracleDbType.Clob:
            //        sqlDbType = System.Data.SqlDbType.Text;
            //        break;
            //    case OracleDbType.Date:
            //        sqlDbType = System.Data.SqlDbType.DateTime;
            //        break;
            //    case OracleDbType.Decimal:
            //        sqlDbType = System.Data.SqlDbType.Decimal;
            //        break;
            //    case OracleDbType.Double:
            //        sqlDbType = System.Data.SqlDbType.Real;
            //        break;
            //    case OracleDbType.Int16:
            //        sqlDbType = System.Data.SqlDbType.SmallInt;
            //        break;
            //    case OracleDbType.Int32:
            //        sqlDbType = System.Data.SqlDbType.Int;
            //        break;
            //    case OracleDbType.Int64:
            //        sqlDbType = System.Data.SqlDbType.BigInt;
            //        break;
            //    case OracleDbType.IntervalDS:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.IntervalYM:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.Long:
            //        sqlDbType = System.Data.SqlDbType.BigInt;
            //        break;
            //    case OracleDbType.LongRaw:
            //        sqlDbType = System.Data.SqlDbType.VarBinary;
            //        break;
            //    case OracleDbType.NChar:
            //        sqlDbType = System.Data.SqlDbType.NChar;
            //        break;
            //    case OracleDbType.NClob:
            //        sqlDbType = System.Data.SqlDbType.NText;
            //        break;
            //    case OracleDbType.NVarchar2:
            //        sqlDbType = System.Data.SqlDbType.NVarChar;
            //        break;
            //    case OracleDbType.Raw:
            //        sqlDbType = System.Data.SqlDbType.VarBinary;
            //        break;
            //    case OracleDbType.RefCursor:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.Single:
            //        sqlDbType = System.Data.SqlDbType.Float;
            //        break;
            //    case OracleDbType.TimeStamp:
            //        sqlDbType = System.Data.SqlDbType.DateTime;
            //        break;
            //    case OracleDbType.TimeStampLTZ:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.TimeStampTZ:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case OracleDbType.Varchar2:
            //        sqlDbType = System.Data.SqlDbType.VarChar;
            //        break;
            //    case OracleDbType.XmlType:
            //        sqlDbType = System.Data.SqlDbType.Xml;
            //        break;
            //    default:
            //        break;
            //}
            //return sqlDbType;
        }

        /// <summary>
        /// convert mssql field data type to oracle field data type
        /// </summary>
        /// <param name="mssqlDbType"></param>
        /// <returns></returns>
        public static OracleDbType ToOracleFieldDataType(System.Data.SqlDbType mssqlDbType)
        {
            return DbTypeConverter.GetOracleDbTypeFromSqlDbType(mssqlDbType);
            //OracleDbType oracleDbType = OracleDbType.Varchar2;
            //switch (mssqlDbType)
            //{
            //    case System.Data.SqlDbType.BigInt:
            //        oracleDbType = OracleDbType.Int64;
            //        break;
            //    case System.Data.SqlDbType.Binary:
            //        oracleDbType = OracleDbType.Blob;
            //        break;
            //    case System.Data.SqlDbType.Bit:
            //        oracleDbType = OracleDbType.Int16;
            //        break;
            //    case System.Data.SqlDbType.Char:
            //        oracleDbType = OracleDbType.Char;
            //        break;
            //    case SqlDbType.Date:
            //        oracleDbType = OracleDbType.Date;
            //        break;
            //    case System.Data.SqlDbType.DateTime:
            //        oracleDbType = OracleDbType.TimeStamp;
            //        break;
            //    case System.Data.SqlDbType.Decimal:
            //        oracleDbType = OracleDbType.Decimal;
            //        break;
            //    case System.Data.SqlDbType.Float:
            //        oracleDbType = OracleDbType.Double;
            //        break;
            //    case System.Data.SqlDbType.Image:
            //        oracleDbType = OracleDbType.BFile;
            //        break;
            //    case System.Data.SqlDbType.Int:
            //        oracleDbType = OracleDbType.Int32;
            //        break;
            //    case System.Data.SqlDbType.Money:
            //        oracleDbType = OracleDbType.Double;
            //        break;
            //    case System.Data.SqlDbType.NChar:
            //        oracleDbType = OracleDbType.NChar;
            //        break;
            //    case System.Data.SqlDbType.NText:
            //        oracleDbType = OracleDbType.NClob;
            //        break;
            //    case System.Data.SqlDbType.NVarChar:
            //        oracleDbType = OracleDbType.NVarchar2;

            //        break;
            //    case System.Data.SqlDbType.Real:
            //        oracleDbType = OracleDbType.Double;
            //        break;
            //    case System.Data.SqlDbType.SmallDateTime:
            //        oracleDbType = OracleDbType.TimeStamp;
            //        break;
            //    case System.Data.SqlDbType.SmallInt:
            //        oracleDbType = OracleDbType.Int16;
            //        break;
            //    case System.Data.SqlDbType.SmallMoney:
            //        oracleDbType = OracleDbType.Decimal;
            //        break;
            //    case System.Data.SqlDbType.Text:
            //        oracleDbType = OracleDbType.Clob;
            //        break;
            //    case System.Data.SqlDbType.Timestamp:
            //        oracleDbType = OracleDbType.TimeStamp;
            //        break;
            //    case System.Data.SqlDbType.TinyInt:
            //        oracleDbType = OracleDbType.Int16;
            //        break;
            //    case System.Data.SqlDbType.Udt:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case System.Data.SqlDbType.UniqueIdentifier:
            //        throw new NotImplementedException("data type conversion not implemented");
            //    case System.Data.SqlDbType.VarBinary:
            //        oracleDbType = OracleDbType.Raw;
            //        break;
            //    case System.Data.SqlDbType.VarChar:
            //        oracleDbType = OracleDbType.Varchar2;
            //        break;
            //    case System.Data.SqlDbType.Variant:
            //        oracleDbType = OracleDbType.Raw;
            //        break;
            //    case System.Data.SqlDbType.Xml:
            //        oracleDbType = OracleDbType.XmlType;
            //        break;
            //    default:
            //        break;
            //}
            //return oracleDbType;
        }

        /// <summary>
        /// string representation of mssql field data type
        /// </summary>
        /// <param name="fieldDataType"></param>
        /// <returns></returns>
        public static string ToMssqlFieldDataTypeString(FieldDataType fieldDataType)
        {
            string dbTypeStr = "nvarchar (255)";
            switch (fieldDataType.DataType)
            {
                case System.Data.SqlDbType.BigInt:
                    dbTypeStr = "bigint";
                    break;
                case System.Data.SqlDbType.Binary:
                    dbTypeStr = "binary";
                    break;
                case System.Data.SqlDbType.Bit:
                    dbTypeStr = "bit";
                    break;
                case System.Data.SqlDbType.Char:
                    dbTypeStr = "char";
                    if (fieldDataType.Size > 0)
                    {
                        dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                    }
                    break;
                case System.Data.SqlDbType.DateTime:
                    dbTypeStr = "datetime";
                    break;
                case System.Data.SqlDbType.Decimal:
                    dbTypeStr = "decimal";
                    break;
                case System.Data.SqlDbType.Float:
                    dbTypeStr = "float";
                    break;
                case System.Data.SqlDbType.Image:
                    dbTypeStr = "image";
                    break;
                case System.Data.SqlDbType.Int:
                    dbTypeStr = "int";
                    break;
                case System.Data.SqlDbType.Money:
                    dbTypeStr = "money";
                    break;
                case System.Data.SqlDbType.NChar:
                    dbTypeStr = "nchar";
                    if (fieldDataType.Size > 0)
                    {
                        fieldDataType.Size = Math.Min(4000, fieldDataType.Size);
                        dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                    }
                    break;
                case System.Data.SqlDbType.NText:
                    dbTypeStr = "ntext";
                    break;
                case System.Data.SqlDbType.NVarChar:
                    dbTypeStr = "nvarchar";
                    if (fieldDataType.Size > 0)
                    {
                        if(fieldDataType.Size>=4000)
                        {
                            dbTypeStr = dbTypeStr + "(max)";
                        }
                        else
                        {
                            dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                        }
                    }
                    break;
                case System.Data.SqlDbType.Real:
                    dbTypeStr = "real";
                    break;
                case System.Data.SqlDbType.SmallDateTime:
                    dbTypeStr = "smalldatetime";
                    break;
                case System.Data.SqlDbType.SmallInt:
                    dbTypeStr = "smallint";
                    break;
                case System.Data.SqlDbType.SmallMoney:
                    dbTypeStr = "smallmoney";
                    break;
                case System.Data.SqlDbType.Text:
                    dbTypeStr = "text";
                    break;
                case System.Data.SqlDbType.Timestamp:
                    dbTypeStr = "timestamp";
                    break;
                case System.Data.SqlDbType.TinyInt:
                    dbTypeStr = "tinyint";
                    break;
                case System.Data.SqlDbType.Udt:
                    dbTypeStr = "udt";
                    break;
                case System.Data.SqlDbType.UniqueIdentifier:
                    dbTypeStr = "uniqueidentifier";
                    break;
                case System.Data.SqlDbType.VarBinary:
                    dbTypeStr = "varbinary";
                    break;
                case System.Data.SqlDbType.VarChar:
                    dbTypeStr = "varchar";
                    if (fieldDataType.Size > 0)
                    {
                        if (fieldDataType.Size >= 8000)
                        {
                            dbTypeStr = dbTypeStr + "(max)";
                        }
                        else
                        {
                            dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                        }
                    }
                    break;
                case System.Data.SqlDbType.Variant:
                    dbTypeStr = "variant";
                    break;
                case System.Data.SqlDbType.Xml:
                    dbTypeStr = "xml";
                    break;
                default:
                    break;
            }
            return dbTypeStr;
        }

        /// <summary>
        /// string representation of oracle field data type
        /// </summary>
        /// <param name="fieldDataType"></param>
        /// <returns></returns>
        public static string ToOracleFieldDataTypeString(FieldDataType fieldDataType)
        {
            string dbTypeStr = "varchar2 (255)";
            OracleDbType oracleDbType = FieldDataType.ToOracleFieldDataType(fieldDataType.DataType);
            switch (oracleDbType)
            {
                case OracleDbType.BFile:
                    dbTypeStr = "bfile";
                    break;
                case OracleDbType.Blob:
                    dbTypeStr = "blob";
                    break;
                case OracleDbType.Char:
                    dbTypeStr = "char";
                    if (fieldDataType.Size > 0)
                    {
                        dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                    }
                    break;
                case OracleDbType.Clob:
                    dbTypeStr = "clob";
                    break;
                case OracleDbType.Date:
                    dbTypeStr = "date";
                    break;
                case OracleDbType.Double:
                    dbTypeStr = "double";
                    if (fieldDataType.Precision > 0)
                    {
                        dbTypeStr = "numeric (" + fieldDataType.Precision + "," + fieldDataType.Scale + ")";
                    }
                    break;
                case OracleDbType.Integer:
                    dbTypeStr = "INTEGER";
                    break;
                case OracleDbType.Long:
                    dbTypeStr = "LONG";
                    break;
                case OracleDbType.IntervalDS:
                    dbTypeStr = "intervalds";
                    break;
                case OracleDbType.IntervalYM:
                    dbTypeStr = "intervalym";
                    break;
                case OracleDbType.LongRaw:
                    dbTypeStr = "longraw";
                    break;
                case OracleDbType.NChar:
                    dbTypeStr = "nchar";
                    if (fieldDataType.Size > 0)
                    {
                        if (fieldDataType.Size >= 4000)
                        {
                            dbTypeStr = "CLOB";
                        }
                        else
                        {
                            dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                        }
                    }
                    break;
                case OracleDbType.NClob:
                    dbTypeStr = "nclob";
                    break;
                case OracleDbType.NVarChar:
                    dbTypeStr = "nvarchar2";
                    if (fieldDataType.Size > 0)
                    {
                        if (fieldDataType.Size > 2000)
                        {
                            dbTypeStr = "CLOB";
                        }
                        else
                        {
                            dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                        }
                    }
                    break;
                case OracleDbType.Raw:
                    dbTypeStr = "raw";
                    break;
                case OracleDbType.TimeStamp:
                    dbTypeStr = "timestamp";
                    break;
                case OracleDbType.TimeStampLTZ:
                    dbTypeStr = "timestampltz";
                    break;
                case OracleDbType.TimeStampTZ:
                    dbTypeStr = "timestamptz";
                    break;
                case OracleDbType.VarChar:
                    dbTypeStr = "varchar2";
                    if (fieldDataType.Size > 0)
                    {
                        if(fieldDataType.Size>2000)
                        {
                            dbTypeStr = "CLOB";
                        }
                        else
                        {
                            dbTypeStr = dbTypeStr + "(" + fieldDataType.Size + ")";
                        }
                    }
                    break;
                case OracleDbType.Xml:
                    dbTypeStr = "xmltype";
                    break;
                default:
                    break;
            }
            
            return dbTypeStr;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fieldDataType"></param>
        /// <returns></returns>
        public static string ToOracleFieldDataTypeStringWithoutSize(FieldDataType fieldDataType)
        {
            string dbTypeStr = "varchar2";
            OracleDbType oracleDbType = FieldDataType.ToOracleFieldDataType(fieldDataType.DataType);
            switch (oracleDbType)
            {
                case OracleDbType.BFile:
                    dbTypeStr = "bfile";
                    break;
                case OracleDbType.Blob:
                    dbTypeStr = "blob";
                    break;
                case OracleDbType.Char:
                    dbTypeStr = "char";
                    break;
                case OracleDbType.Clob:
                    dbTypeStr = "clob";
                    break;
                case OracleDbType.Date:
                    dbTypeStr = "date";
                    break;
                case OracleDbType.Double:
                    dbTypeStr = "double";
                    if (fieldDataType.Precision > 0)
                    {
                        dbTypeStr = "numeric";
                    }
                    break;
                case OracleDbType.Integer:
                    dbTypeStr = "INTEGER";

                    break;
                case OracleDbType.IntervalDS:
                    dbTypeStr = "intervalds";
                    break;
                case OracleDbType.IntervalYM:
                    dbTypeStr = "intervalym";
                    break;
                case OracleDbType.Long:
                    dbTypeStr = "long";

                    break;
                case OracleDbType.LongRaw:
                    dbTypeStr = "longraw";
                    break;
                case OracleDbType.NChar:
                    dbTypeStr = "nchar";
                    if (fieldDataType.Size > 0)
                    {
                        if (fieldDataType.Size >= 4000)
                        {
                            dbTypeStr = "CLOB";
                        }
                    }
                    break;
                case OracleDbType.NClob:
                    dbTypeStr = "nclob";
                    break;
                case OracleDbType.Number:
                    dbTypeStr = string.Format("number ({0},{1})", fieldDataType.Precision, fieldDataType.Scale);
                    break;
                case OracleDbType.NVarChar:
                    dbTypeStr = "nvarchar2";
                    if (fieldDataType.Size > 0)
                    {
                        if (fieldDataType.Size > 2000)
                        {
                            dbTypeStr = "CLOB";
                        }
                    }
                    break;
                case OracleDbType.Raw:
                    dbTypeStr = "raw";
                    break;
                case OracleDbType.TimeStamp:
                    dbTypeStr = "timestamp";
                    break;
                case OracleDbType.TimeStampLTZ:
                    dbTypeStr = "timestampltz";
                    break;
                case OracleDbType.TimeStampTZ:
                    dbTypeStr = "timestamptz";
                    break;
                case OracleDbType.VarChar:
                    dbTypeStr = "varchar2";
                    if (fieldDataType.Size > 0)
                    {
                        if (fieldDataType.Size > 2000)
                        {
                            dbTypeStr = "CLOB";
                        }
                    }
                    break;
                case OracleDbType.Xml:
                    dbTypeStr = "xmltype";
                    break;
                default:
                    break;
            }

            return dbTypeStr;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fieldDataType"></param>
        /// <returns></returns>
        public static string ToString(FieldDataType fieldDataType)
        {
            string fieldDbTypeString = string.Empty;
            switch (fieldDataType.Provider)
            {
                case DataProviderType.MSSQL:
                    fieldDbTypeString = FieldDataType.ToMssqlFieldDataTypeString(fieldDataType);
                    break;
                case DataProviderType.Oracle:
                    fieldDbTypeString = FieldDataType.ToOracleFieldDataTypeString(fieldDataType);
                    break;
                default:
                    fieldDbTypeString = FieldDataType.ToMssqlFieldDataTypeString(fieldDataType);
                    break;
            }
            return fieldDbTypeString;
        }

        /// <summary>
        /// system type -> sql db type
        /// </summary>
        /// <param name="typeStr"></param>
        /// <returns></returns>
        public static System.Data.SqlDbType ToDbType(string typeStr)
        {
            System.Data.SqlDbType dbType = System.Data.SqlDbType.NVarChar;
            if (typeStr.ToLower() == typeof(Int64).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.BigInt;
            }
            else if (typeStr.ToLower() == typeof(byte[]).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Image;
            }
            else if (typeStr.ToLower() == typeof(bool).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Bit;
            }
            else if (typeStr.ToLower() == typeof(byte).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.TinyInt;
            }
            else if (typeStr.ToLower() == typeof(byte[]).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Binary;
            }
            else if (typeStr.ToLower() == typeof(Int16).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.SmallInt;
            }
            else if (typeStr.ToLower() == typeof(char).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Char;
            }
            else if (typeStr.ToLower() == typeof(DateTime).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.DateTime;
            }
            else if (typeStr.ToLower() == typeof(Decimal).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Decimal;
            }
            else if (typeStr.ToLower() == typeof(double).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Real;
            }
            else if (typeStr.ToLower() == typeof(float).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Float;
            }
            else if (typeStr.ToLower() == typeof(Int32).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Int;
            }
            else if (typeStr.ToLower() == typeof(int).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Int;
            }
            else if (typeStr.ToLower() == typeof(char[]).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.Text;
            }
            else if (typeStr.ToLower() == typeof(System.Guid).ToString().ToLower())
            {
                dbType = System.Data.SqlDbType.UniqueIdentifier;
            }
            return dbType;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool IsStringType(System.Data.SqlDbType dbType)
        {
            bool isStringType = false;
            switch (dbType)
            {
                case System.Data.SqlDbType.Char:
                case System.Data.SqlDbType.NChar:
                case System.Data.SqlDbType.NVarChar:
                case System.Data.SqlDbType.VarChar:
                    isStringType = true;
                    break;
                default:
                    break;
            }
            return isStringType;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool IsTextType(System.Data.SqlDbType dbType)
        {
            bool isTextType = false;
            switch (dbType)
            {
                case System.Data.SqlDbType.NText:
                case System.Data.SqlDbType.Text:
                    isTextType = true;
                    break;
                default:
                    break;
            }
            return isTextType;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool IsBinaryType(System.Data.SqlDbType dbType)
        {
            bool isBinaryType = false;
            switch (dbType)
            {
                case System.Data.SqlDbType.Binary:
                case System.Data.SqlDbType.Image:
                case System.Data.SqlDbType.Udt:
                case System.Data.SqlDbType.VarBinary:
                case System.Data.SqlDbType.Variant:
                    isBinaryType = true;
                    break;
                default:
                    break;
            }
            return isBinaryType;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool IsNumber(System.Data.SqlDbType dbType)
        {
            bool isDbTypeNumber = false;
            switch (dbType)
            {
                case System.Data.SqlDbType.BigInt:
                case System.Data.SqlDbType.Decimal:
                case System.Data.SqlDbType.Float:
                case System.Data.SqlDbType.Int:
                case System.Data.SqlDbType.Money:
                case System.Data.SqlDbType.Real:
                case System.Data.SqlDbType.SmallInt:
                case System.Data.SqlDbType.SmallMoney:
                case System.Data.SqlDbType.TinyInt:
                    isDbTypeNumber = true;
                    break;
                default:
                    isDbTypeNumber = false;
                    break;
            }
            return isDbTypeNumber;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool isBoolean(System.Data.SqlDbType dbType)
        {
            if (dbType == System.Data.SqlDbType.Bit)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool IsDateTime(System.Data.SqlDbType dbType)
        {
            bool isDbTypeDateTime = false;
            switch (dbType)
            {
                case System.Data.SqlDbType.DateTime:
                case System.Data.SqlDbType.SmallDateTime:
                    isDbTypeDateTime = true;
                    break;
                default:
                    isDbTypeDateTime = false;
                    break;
            }
            return isDbTypeDateTime;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static Type ToSystemType(System.Data.SqlDbType dbType)
        {
            Type sysType = typeof (string);
            switch (dbType)
            {
                case System.Data.SqlDbType.Binary:
                    sysType = typeof (byte[]);
                    break;
                case System.Data.SqlDbType.Bit:
                    sysType = typeof (bool);
                    break;
                case System.Data.SqlDbType.Char:
                    sysType = typeof (string);
                    break;
                case System.Data.SqlDbType.DateTime:
                    sysType = typeof (DateTime);
                    break;
                case System.Data.SqlDbType.Image:
                    sysType = typeof(byte[]);
                    break;
                case System.Data.SqlDbType.NChar:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.NText:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.NVarChar:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.SmallDateTime:
                    sysType = typeof(DateTime);
                    break;
                case System.Data.SqlDbType.Text:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.Timestamp:
                    sysType = typeof(DateTime);
                    break;
                case System.Data.SqlDbType.Udt:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.UniqueIdentifier:
                    sysType = typeof(Guid);
                    break;
                case System.Data.SqlDbType.VarBinary:
                    sysType = typeof(byte[]);
                    break;
                case System.Data.SqlDbType.VarChar:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.Variant:
                    sysType = typeof(byte[]);
                    break;
                case System.Data.SqlDbType.Xml:
                    sysType = typeof(string);
                    break;
                case System.Data.SqlDbType.BigInt:
                    sysType = typeof (Int64);
                    break;
                case System.Data.SqlDbType.Decimal:
                    sysType = typeof (decimal);
                    break;
                case System.Data.SqlDbType.Float:
                    sysType = typeof (Single);
                    break;
                case System.Data.SqlDbType.Int:
                    sysType = typeof (int);
                    break;
                case System.Data.SqlDbType.Money:
                    sysType = typeof (decimal);
                    break;
                case System.Data.SqlDbType.Real:
                    sysType = typeof (decimal);
                    break;
                case System.Data.SqlDbType.SmallInt:
                    sysType = typeof (Int16);
                    break;
                case System.Data.SqlDbType.SmallMoney:
                    sysType = typeof (Single);
                    break;
                case System.Data.SqlDbType.TinyInt:
                    sysType = typeof (Int16);
                    break;
                default:
                    break;
            }
            return sysType;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="dbValue"></param>
        /// <returns></returns>
        public static string ToOracleDataValue(System.Data.SqlDbType dbType, string dbValue)
        {
            if (dbValue == null)
            {
                return "null";
            }
            else
            {
                if (FieldDataType.IsNumber(dbType))
                {
                    if (dbValue.Length == 0)
                        return "null";
                    else
                        return dbValue;
                }
                else if (FieldDataType.isBoolean(dbType))
                {
                    if (dbValue.Length == 0)
                        return "null";
                    else
                    {
                        bool boolValue = bool.Parse(dbValue);
                        if (boolValue)
                            return "1";
                        else
                            return "0";
                    }
                }
                else if (FieldDataType.IsDateTime(dbType))
                {
                    if (dbValue.Length == 0)
                    {
                        return "null";
                    }
                    else
                    {
                        DateTime dateValue = DateTime.Parse(dbValue);
                        string dateStr = dateValue.Year + "-" +
                            dateValue.Month + "-" +
                            dateValue.Day + " " +
                            dateValue.Hour + ":" +
                            dateValue.Minute + ":" +
                            dateValue.Second;
                        string oraDateValue =
                            "to_timestamp('" + dateStr +
                            "','YYYY-MM-DD HH24:MI:SS')";
                        return oraDateValue;
                    }
                }
                else
                {
                    return "'" + dbValue.Replace("'", "''") + "'";
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="dbValue"></param>
        /// <returns></returns>
        public static string ToMssqlDataValue(System.Data.SqlDbType dbType, string dbValue)
        {
            if (dbValue == null)
                return "null";
            else
            {
                if (FieldDataType.IsNumber(dbType))
                {
                    if (dbValue.Length == 0)
                        return "null";
                    else
                        return dbValue;
                }
                else if (FieldDataType.isBoolean(dbType))
                {
                    if (dbValue.Length == 0)
                        return "null";
                    else
                    {
                        bool boolValue = bool.Parse(dbValue);
                        if (boolValue)
                            return "1";
                        else
                            return "0";
                    }
                }
                else if (FieldDataType.IsDateTime(dbType))
                {
                    if (dbValue.Length == 0)
                    {
                        return "null";
                    }
                    else
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Parse(dbValue);
                            dateTime = dateTime < TimeUtil.DbMinTime ? TimeUtil.DbMinTime : dateTime;
                            dbValue = dateTime.ToString();
                            return "'" + dbValue + "'";
                        }
                        catch (Exception)
                        {
                            return "null";
                        }
                    }
                }
                else
                {
                    return "'" + dbValue.Replace("'", "''") + "'";
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static int GetFieldDefaultSize(SqlDbType dbType)
        {
            int size = 255;

            switch (dbType)
            {
                case System.Data.SqlDbType.Binary:
                    size = 0;   // default value or max
                    break;
                case System.Data.SqlDbType.Bit:
                    size = 1;   
                    break;
                case System.Data.SqlDbType.Char:
                    size = 50;   
                    break;
                case System.Data.SqlDbType.DateTime:
                    size = 8;
                    break;
                case System.Data.SqlDbType.Image:
                    size = 0;
                    break;
                case System.Data.SqlDbType.NChar:
                    size = 50;
                    break;
                case System.Data.SqlDbType.NText:
                    size = 0;
                    break;
                case System.Data.SqlDbType.NVarChar:
                    size = 255;
                    break;
                case System.Data.SqlDbType.SmallDateTime:
                    size = 4;
                    break;
                case System.Data.SqlDbType.Text:
                    size = 0;
                    break;
                case System.Data.SqlDbType.Timestamp:
                    size = 8;
                    break;
                case System.Data.SqlDbType.Udt:
                    size = 0;
                    break;
                case System.Data.SqlDbType.UniqueIdentifier:
                    size = 16;
                    break;
                case System.Data.SqlDbType.VarBinary:
                    size = 0;
                    break;
                case System.Data.SqlDbType.VarChar:
                    size = 255;
                    break;
                case System.Data.SqlDbType.Variant:
                    size = 0;
                    break;
                case System.Data.SqlDbType.Xml:
                    size = 0;
                    break;
                case System.Data.SqlDbType.BigInt:
                    size = 8;
                    break;
                case System.Data.SqlDbType.Decimal:
                    size = 8;
                    break;
                case System.Data.SqlDbType.Float:
                    size = 8;
                    break;
                case System.Data.SqlDbType.Int:
                    size = 4;
                    break;
                case System.Data.SqlDbType.Money:
                    size = 0;
                    break;
                case System.Data.SqlDbType.Real:
                    size = 8;
                    break;
                case System.Data.SqlDbType.SmallInt:
                    size = 2;
                    break;
                case System.Data.SqlDbType.SmallMoney:
                    size = 0;
                    break;
                case System.Data.SqlDbType.TinyInt:
                    size = 1;
                    break;
                default:
                    break;
            }

            return size;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="simpleDataType"></param>
        /// <returns></returns>
        public static SqlDbType ToSqlDbType(SimpleDataType simpleDataType)
        {
            switch(simpleDataType)
            {
                case SimpleDataType.DateTimeType:
                    return SqlDbType.DateTime;
                case SimpleDataType.DecimalNumberType:
                    return SqlDbType.Decimal;
                case SimpleDataType.DoubleNumberType:
                    return SqlDbType.Decimal;
                case SimpleDataType.EnumType:
                    return SqlDbType.Int;
                case SimpleDataType.GuidType:
                    return SqlDbType.UniqueIdentifier;
                case SimpleDataType.IntListType:
                    return SqlDbType.VarChar;
                case SimpleDataType.IntNumberType:
                    return SqlDbType.Int;
                case SimpleDataType.StringListType:
                    return SqlDbType.VarChar;
                case SimpleDataType.TextType:
                    return SqlDbType.VarChar;
                case SimpleDataType.YesNoType:
                    return SqlDbType.Bit;
                default:
                    return SqlDbType.VarChar;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlDbType"></param>
        /// <returns></returns>
        public static bool CanSort(SqlDbType sqlDbType)
        {
            switch (sqlDbType)
            {
                case System.Data.SqlDbType.Binary:
                case System.Data.SqlDbType.Image:
                case System.Data.SqlDbType.NText:
                case System.Data.SqlDbType.Text:
                case System.Data.SqlDbType.Udt:
                case System.Data.SqlDbType.UniqueIdentifier:
                case System.Data.SqlDbType.VarBinary:
                case System.Data.SqlDbType.Variant:
                case System.Data.SqlDbType.Xml:
                    return false;
                default:
                    return true;
            }
        }
    }
}
